package henu.javaweb.dao;

import java.sql.*;
import java.util.ArrayList;

import javax.websocket.Session;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import henu.javaweb.action.IStudentDao;
import henu.javaweb.bean.Student;
import henu.javaweb.util.WebDbPool;

public class StudentDao implements IStudentDao {
	
	private QueryRunner queryRunner=new QueryRunner();
	@Override
	public Student login(String sno, String password) {
		// TODO Auto-generated method stub
		String sql="select * from tb_student where s_no='"+sno+"' and s_password='"+password+"'";
		Student student=null;
		try {
			student=queryRunner.query(WebDbPool.getConnection(), sql,new BeanHandler<Student>(Student.class));
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		return student;
	}
	@Override
	public int update(Student student) {
		// TODO Auto-generated method stub
        int result=0;
		
		String sql="update tb_student set s_no=?,s_name=?,s_password=?,s_nameUsed=?,s_spellname=?,s_gender=?,"
				+ "s_IDCard=?,s_birthdate=?,s_nation=?,s_nativePlace=?,s_politicalStatus=?,s_phone=?,"
				+ "s_email=?,s_faculty=?,s_major=?,s_remarks=?,s_photo=?,t_id=? ";
		Object params[]= {student.getS_no(),student.getS_name(),student.getS_password(),student.getS_nameUsed(),student.getS_spellname(),
				student.getS_gender(),student.getS_IDCard(),student.getS_birthdate(),student.getS_nation(),
				student.getS_nativePlace(),student.getS_politicalStatus(),student.getS_phone(),student.getS_email(),
				student.getS_faculty(),student.getS_major(),student.getS_remarks(),student.getS_photo(),student.getT_id()
				};

		try {
			result=queryRunner.update(WebDbPool.getConnection(), sql, params);
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		WebDbPool.close();
		return result;
	}
	@Override
	public boolean updateStuPass(String sno,String spass) {
		// TODO Auto-generated method stub
		String sql="update tb_student set s_password=? where s_no=?";
		int result=0;
		try {
			PreparedStatement ps=WebDbPool.executePreparedStatement(sql);
			ps.setString(1, spass);
			ps.setString(2, sno);
			result=ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result>0;
	}
	@Override
	public Student findBySno(String sno) {
		// TODO Auto-generated method stub
		Student student=null;
		String sql="select * from tb_student where s_no='"+sno+"'";
		try {
			student=queryRunner.query(WebDbPool.getConnection(), sql, new BeanHandler<Student>(Student.class));
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		return student;
	}
	@Override
	public ArrayList<Student> getAllStudent() {
		ArrayList<Student> students=new ArrayList<>();
		String sql="SELECT * FROM tb_student";
		try {
			students=(ArrayList<Student>) queryRunner.query(WebDbPool.getConnection(), sql, new BeanListHandler<Student>(Student.class));
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return students;
	}
	@Override
	public boolean addStudent(Student s)  {
		String sql = "INSERT INTO tb_student (s_no,s_name,s_password,s_faculty,s_major,t_id) " + "VALUES (?,?,?,?,?,?)";
		try {
		PreparedStatement ps = WebDbPool.executePreparedStatement(sql);
		int result = 0;
		ps.setString(1, s.getS_no());
			ps.setString(2, s.getS_name());
			ps.setString(3, s.getS_password());
			ps.setString(4, s.getS_faculty());
			ps.setString(5, s.getS_major());
			ps.setString(6, s.getT_id());
			result = ps.executeUpdate();
			ps.close();
			WebDbPool.close();
			return true;
		} catch (SQLException e) {
			return false;
		}
	}
	@Override
	public boolean deleteStudent(String sno) {
		String sql = "delete from tb_student where s_no = ?;";
        try {
            // 预编译
            PreparedStatement ps = WebDbPool.executePreparedStatement(sql);
            if(sno!=null) {
            	ps.setString(1, sno);
            // 执行
            ps.executeUpdate();}
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
	}
	
}
